package web.chiTietSP;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.print.attribute.standard.Chromaticity;

public class connectDB {
	Connection connect = null;
	Statement stmt = null;
	ResultSet rs = null;

	/**
	 * Check Driver connect to MySQL
	 */
	protected void hasDriver() throws Exception {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException ex) {
			throw new Exception("Invalid Driver!!Please check this drver....");
		}
	}

	/**
	 * Function used to get the connection to the Database Step 1 - I check my
	 * connection or not!! Step 2 - If not, it will be null and initialization.
	 * Step 3 - Then it return
	 * 
	 * @return Connection
	 */
	protected Connection openConnect() throws Exception {
		if (connect == null) {
			hasDriver();
			String url = "jdbc:mysql://localhost/web";
			// url_sql = "....";
			try {
				this.connect = DriverManager
						.getConnection(url, "root", "12345");
			} catch (SQLException e) {
				throw new Exception(e.getMessage()
						+ "Connect failed to database .... ");
			}
		}
		return connect;
	}

	/**
	 * Make a Statement to execute the SQL statement
	 * 
	 * @return Statement
	 */
	protected Statement getStatement() throws SQLException, Exception {
		if (stmt == null) {
			stmt = openConnect().createStatement();
		}
		return stmt;
	}

	/**
	 * Used to execute the Select statement
	 * 
	 * @param strSQL
	 *            Query VD: Select * from Employee
	 * @return ResultSet
	 */
	// thuc hien cac cau lenh cho data sanpham
	public ArrayList<chiTietSP> getAllChiTietSPs() throws Exception {
		ArrayList<chiTietSP> lst = new ArrayList<chiTietSP>();
		String strSQL = "select * from chitietsp";
		try {
			rs = getStatement().executeQuery(strSQL);
			while (rs.next()) {
				String msp = rs.getString("masp");
				String mctsp = rs.getString("mactsp");
				String ms = rs.getString("mausac");
				double gia = Double.parseDouble(rs.getString("gia"));
				float size = Float.parseFloat(rs.getString("size"));
				String hinhsp = rs.getString("hinh");
				chiTietSP ctsp = new chiTietSP(msp, mctsp, ms, gia, size,
						hinhsp);
				lst.add(ctsp);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage() + " Error at : " + strSQL);
		}
		closeConnet();
		return lst;
	}

	public boolean insertNew(chiTietSP ctsp) throws Exception {
		String sql = "insert into chitietsp values(?,?,?,?,?,?)";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		pst.setString(1, ctsp.getMasp());
		pst.setString(2, ctsp.getMctsp());
		pst.setString(3, ctsp.getMausac());
		pst.setDouble(4, ctsp.getGia());
		pst.setFloat(5, ctsp.getSize());
		pst.setString(6, ctsp.getHinh());
		return pst.executeUpdate() > 0;

	}

	public boolean deleteCTSP(String msp, String mctsp) throws Exception {
		String sql = "delete from chitietsp where masp=?,mactsp=?";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		pst.setString(1, msp);
		pst.setString(2, mctsp);
		return pst.executeUpdate() > 0;
	}

	/**
	 * Used to execute the Insert, Update, Delete statement
	 * 
	 * @param strSQL
	 *            Query VD: Insert into TableName values ('??','??')
	 * @return The number of lines affected by the command
	 */
	public int executeUpdate(String strSQL) throws Exception {
		int result = 0;
		try {
			result = getStatement().executeUpdate(strSQL);
		} catch (Exception ex) {
			throw new Exception(ex.getMessage() + " Error at: " + strSQL);
		} finally {
			this.closeConnet();
		}
		return result;
	}

	public chiTietSP getChiTietSP_ID(String msp, String mctsp) throws Exception {
		String sql = "select * from chitietsp where masp=?,mactsp=?";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		pst.setString(1, msp);
		pst.setString(2, mctsp);
		ResultSet rs = pst.executeQuery();
		chiTietSP ctsp = null;
		if (rs.next()) {
			String masp = rs.getString("masp");
			String mactsp = rs.getString("mactsp");
			String ms = rs.getString("mausac");
			double gia = Double.parseDouble(rs.getString("gia"));
			float size = Float.parseFloat(rs.getString("size"));
			String hinhsp = rs.getString("hinh");
			ctsp = new chiTietSP(msp, mctsp, ms, gia, size, hinhsp);

		}
		return ctsp;
	}

	public boolean UpdateCTSP(String masp, String mactsp, chiTietSP newCtsp)
			throws Exception {
		String sql = "update chitietsp set mausac=?,gia=?,size=?,hinh=? where masp=?,mactsp=?";
		PreparedStatement pst = openConnect().prepareStatement(sql);
		pst.setString(1, newCtsp.getMausac());
		pst.setDouble(2, newCtsp.getGia());
		pst.setFloat(3, newCtsp.getSize());
		pst.setString(4, newCtsp.getHinh());
		pst.setString(5, newCtsp.getMasp());
		pst.setString(6, newCtsp.getMctsp());
		return pst.executeUpdate() > 0;
	}

	/**
	 * A method to close the connection.
	 * 
	 * @throws SQLException
	 */
	public void closeConnet() throws SQLException {
		if (rs != null && !rs.isClosed())
			rs.close();
		if (stmt != null)
			stmt.close();
		if (connect != null)
			connect.close();
	}
}
